-- Things IoT Platform 数据库初始化脚本

-- 创建扩展
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- 创建用户表
CREATE TABLE IF NOT EXISTS users (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    email VARCHAR(100) UNIQUE,
    role VARCHAR(20) DEFAULT 'user', -- 'super_admin', 'user'
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 创建设备表
CREATE TABLE IF NOT EXISTS devices (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    device_id UUID UNIQUE NOT NULL DEFAULT gen_random_uuid(), -- MQTT Client ID, 使用UUID
    name VARCHAR(100) NOT NULL,
    description TEXT,
    device_type VARCHAR(20) DEFAULT 'direct', -- 'direct', 'gateway'
    status VARCHAR(20) DEFAULT 'offline', -- 'online', 'offline', 'error'
    last_seen TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 创建设备遥测数据表 (TimescaleDB)
CREATE TABLE IF NOT EXISTS device_telemetry (
    time TIMESTAMPTZ NOT NULL,
    device_id BIGINT NOT NULL,
    telemetry_data JSONB NOT NULL -- 存储完整的遥测数据JSON
);

-- 创建超表
SELECT create_hypertable('device_telemetry', 'time', if_not_exists => TRUE);

-- 创建设备事件表
CREATE TABLE IF NOT EXISTS device_events (
    time TIMESTAMPTZ NOT NULL,
    device_id BIGINT NOT NULL,
    event_type VARCHAR(50) NOT NULL, -- 'connect', 'disconnect', 'error', 'command'
    event_data JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 创建超表
SELECT create_hypertable('device_events', 'time', if_not_exists => TRUE);

-- 创建设备命令表
CREATE TABLE IF NOT EXISTS device_commands (
    id BIGSERIAL PRIMARY KEY,
    device_id BIGINT NOT NULL REFERENCES devices(id) ON DELETE CASCADE,
    command_id VARCHAR(100) NOT NULL,
    command VARCHAR(100) NOT NULL,
    target VARCHAR(100) DEFAULT 'self', -- 'self' 或子设备标识符
    parameters JSONB,
    status VARCHAR(20) DEFAULT 'pending', -- 'pending', 'sent', 'completed', 'failed'
    sent_at TIMESTAMPTZ,
    completed_at TIMESTAMPTZ,
    response JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 创建索引
CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
CREATE INDEX IF NOT EXISTS idx_devices_user_id ON devices(user_id);
CREATE INDEX IF NOT EXISTS idx_devices_device_id ON devices(device_id);
CREATE INDEX IF NOT EXISTS idx_devices_status ON devices(status);
CREATE INDEX IF NOT EXISTS idx_telemetry_device_time ON device_telemetry(device_id, time DESC);
CREATE INDEX IF NOT EXISTS idx_events_device_time ON device_events(device_id, time DESC);
CREATE INDEX IF NOT EXISTS idx_events_event_type ON device_events(event_type);
CREATE INDEX IF NOT EXISTS idx_commands_device_status ON device_commands(device_id, status);
CREATE INDEX IF NOT EXISTS idx_commands_command_id ON device_commands(command_id);

-- 创建函数：更新设备最后在线时间
CREATE OR REPLACE FUNCTION update_device_last_seen()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE devices 
    SET last_seen = NOW(), 
        status = 'online',
        updated_at = NOW()
    WHERE id = NEW.device_id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器：当设备连接事件发生时更新设备状态
CREATE OR REPLACE TRIGGER trigger_device_connect
    AFTER INSERT ON device_events
    FOR EACH ROW
    WHEN (NEW.event_type = 'connect')
    EXECUTE FUNCTION update_device_last_seen();

-- 创建函数：清理旧数据
CREATE OR REPLACE FUNCTION cleanup_old_telemetry()
RETURNS VOID AS $$
BEGIN
    -- 删除超过5000条的旧遥测数据
    DELETE FROM device_telemetry 
    WHERE time < (
        SELECT time 
        FROM device_telemetry 
        ORDER BY time DESC 
        LIMIT 1 OFFSET 5000
    );
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION cleanup_old_events()
RETURNS VOID AS $$
BEGIN
    -- 删除超过1000条的旧事件数据
    DELETE FROM device_events 
    WHERE time < (
        SELECT time 
        FROM device_events 
        ORDER BY time DESC 
        LIMIT 1 OFFSET 1000
    );
END;
$$ LANGUAGE plpgsql;

-- 创建默认管理员用户
INSERT INTO users (username, password_hash, email, role) 
VALUES (
    'admin', 
    '$2a$10$8K1p/a0dRTlB0ZQ1KQ1KQe1KQ1KQ1KQ1KQ1KQ1KQ1KQ1KQ1KQ1KQ1K', -- 密码: admin123
    'admin@things-iot.com', 
    'super_admin'
) ON CONFLICT (username) DO NOTHING;

-- 创建示例设备
INSERT INTO devices (user_id, name, description, device_type) 
SELECT id, '示例温度传感器', '办公室温度传感器示例', 'direct'
FROM users WHERE username = 'admin'
ON CONFLICT DO NOTHING;

-- 创建视图：设备状态概览
CREATE OR REPLACE VIEW device_status_overview AS
SELECT 
    d.id,
    d.device_id,
    d.name,
    d.device_type,
    d.status,
    d.last_seen,
    u.username as owner,
    (SELECT COUNT(*) FROM device_telemetry dt WHERE dt.device_id = d.id) as telemetry_count,
    (SELECT COUNT(*) FROM device_events de WHERE de.device_id = d.id) as events_count
FROM devices d
JOIN users u ON d.user_id = u.id;

-- 输出初始化完成信息
DO $$
BEGIN
    RAISE NOTICE 'Things IoT Platform 数据库初始化完成';
    RAISE NOTICE '默认管理员账号: admin / admin123';
    RAISE NOTICE '请在生产环境中修改默认密码!';
END $$;